namespace CISLib
{
    using System;
    using System.Collections;
    using System.Data;
    using System.Data.SqlClient;
    using System.Text;

    public class ManageUser
    {
        public static SqlDataReader GetUser()
        {
            string strSQL = "Select * from tb_SysUser order by UserName";
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            return MyDB.DBReader;
        }

        public static SqlDataReader GetUser(Guid areaId)
        {
            string strSQL = "select * from dbo.TB_SysUser where UserId in ( Select DISTINCT UserId from dbo.TB_UserArea where AreaId=@AreaId )";
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(areaId, "AreaId", SqlDbType.UniqueIdentifier);
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            return MyDB.DBReader;
        }

        public static SqlDataReader GetUser(Guid areaId, Guid regionId, Guid catId)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("select * from dbo.TB_SysUser  ");
            sb.Append("where UserId in                  ");
            sb.Append("( Select DISTINCT UserId from dbo.TB_UserArea where AreaId=@AreaId )   ");
            sb.Append("and                                ");
            sb.Append("userId in                            ");
            sb.Append("(Select DISTINCT UserId from dbo.TB_UserRegion where RegionId=@RegionId)   ");
            sb.Append("and                                      ");
            sb.Append("userId in                                 ");
            sb.Append("(Select DISTINCT UserId from dbo.TB_UserCAT where CatId=@CatId)            ");
            string strSQL = sb.ToString();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(areaId, "AreaId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(regionId, "RegionId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(catId, "CatId", SqlDbType.UniqueIdentifier);
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            return MyDB.DBReader;
        }

        public static SqlDataReader GetUser(Guid areaId, string strProvince, string strCat, string strRole, string strSpell)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(areaId, "AreaId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(new Guid(strProvince), "RegionId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(new Guid(strCat), "CatId", SqlDbType.UniqueIdentifier);
            if (strRole != "All")
            {
                MyDB.AddParameters(new Guid(strRole), "RoleId", SqlDbType.UniqueIdentifier);
            }
            MyDB.AddParameters(strSpell, "FirstName", SqlDbType.VarChar);
            StringBuilder sb = new StringBuilder("");
            sb.Append("select * from dbo.TB_SysUser  ");
            sb.Append("     where                                                                 ");
            int posWhereExpressionStart = sb.ToString().Length;
            sb.Append("UserId in                                                           ");
            sb.Append("( Select DISTINCT UserId from dbo.TB_UserArea where AreaId=@AreaId )   ");
            if (strProvince != "00000000-0000-0000-0000-000000000000")
            {
                sb.Append("and                                                                    ");
                sb.Append("userId in                                                               ");
                sb.Append("(Select DISTINCT UserId from dbo.TB_UserRegion where RegionId=@RegionId)   ");
            }
            if (strCat != "00000000-0000-0000-0000-000000000000")
            {
                sb.Append("and                                                                     ");
                sb.Append("userId in                                                              ");
                sb.Append("(Select DISTINCT UserId from dbo.TB_UserCAT where CatId=@CatId)            ");
            }
            if (strRole != "All")
            {
                sb.Append("and                                                                    ");
                sb.Append("TB_SysUser.RoleId =@RoleId                                             ");
            }
            if (strSpell != "All")
            {
                sb.Append("and                                                                       ");
                sb.Append("substring(tb_SysUser.LoginName,1,1)=@FirstName                             ");
            }
            sb.Append("   order by  LoginName                                                     ");
            string strSQL = sb.ToString();
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            return MyDB.DBReader;
        }

        public static SysUser[] GetUserAryViaRoleId(Guid roleId)
        {
            SqlDataReader MyReader = null;
            ArrayList userList = new ArrayList();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(roleId, "RoleId", SqlDbType.UniqueIdentifier);
            string strSQL = "Select * from TB_SysUser where RoleId=@RoleId order by UserEmail";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            MyReader = MyDB.DBReader;
            while (MyReader.Read())
            {
                SysUser sysUser = new SysUser();
                sysUser.UserId = MyReader.GetGuid(0);
                sysUser.LoginName = MyReader.GetString(1);
                sysUser.UserName = MyReader.GetString(2);
                sysUser.UserEmail = MyReader.GetString(3);
                sysUser.RoleId = MyReader.GetGuid(6);
                sysUser.IsAdmin = MyReader.GetInt32(7);
                sysUser.IsDownload = MyReader.GetInt32(8);
                userList.Add(sysUser);
            }
            MyDB.DBClose();
            return (SysUser[]) userList.ToArray(typeof(SysUser));
        }

        public static string GetUserCount(Guid areaId, string strProvince, string strCat, string strRole, string strSpell)
        {
            try
            {
                DBAccess MyDB = new DBAccess();
                MyDB.DBOpen();
                MyDB.AddParameters(areaId, "AreaId", SqlDbType.UniqueIdentifier);
                MyDB.AddParameters(new Guid(strProvince), "RegionId", SqlDbType.UniqueIdentifier);
                MyDB.AddParameters(new Guid(strCat), "CatId", SqlDbType.UniqueIdentifier);
                if (strRole != "All")
                {
                    MyDB.AddParameters(new Guid(strRole), "RoleId", SqlDbType.UniqueIdentifier);
                }
                MyDB.AddParameters(strSpell, "FirstName", SqlDbType.VarChar);
                StringBuilder sb = new StringBuilder("");
                sb.Append("select count(*) from dbo.TB_SysUser  ");
                sb.Append("     where                                                                 ");
                int posWhereExpressionStart = sb.ToString().Length;
                sb.Append("UserId in                                                           ");
                sb.Append("( Select DISTINCT UserId from dbo.TB_UserArea where AreaId=@AreaId )   ");
                if (strProvince != "00000000-0000-0000-0000-000000000000")
                {
                    sb.Append("and                                                                    ");
                    sb.Append("userId in                                                               ");
                    sb.Append("(Select DISTINCT UserId from dbo.TB_UserRegion where RegionId=@RegionId)   ");
                }
                if (strCat != "00000000-0000-0000-0000-000000000000")
                {
                    sb.Append("and                                                                     ");
                    sb.Append("userId in                                                              ");
                    sb.Append("(Select DISTINCT UserId from dbo.TB_UserCAT where CatId=@CatId)            ");
                }
                if (strRole != "All")
                {
                    sb.Append("and                                                                    ");
                    sb.Append("TB_SysUser.RoleId =@RoleId                                             ");
                }
                if (strSpell != "All")
                {
                    sb.Append("and                                                                       ");
                    sb.Append("substring(tb_SysUser.LoginName,1,1)=@FirstName                             ");
                }
                string strSQL = sb.ToString();
                MyDB.DBSetSelect(strSQL);
                string result = MyDB.DBExecuteScalar().ToString();
                MyDB.DBClose();
                return result;
            }
            catch (Exception)
            {
            }
            return "0";
        }

        public static SysUser GetUserViaId(Guid id)
        {
            SqlDataReader MyReader = null;
            SysUser sysUser = new SysUser();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            string strSQL = "select * from TB_SysUser where UserId = @UserId";
            MyDB.AddParameters(id, "UserId", SqlDbType.UniqueIdentifier);
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            MyReader = MyDB.DBReader;
            if (MyReader.Read())
            {
                sysUser.UserId = MyReader.GetGuid(0);
                sysUser.LoginName = MyReader.GetString(1);
                sysUser.UserName = MyReader.GetString(2);
                sysUser.UserEmail = MyReader.GetString(3);
                sysUser.RoleId = MyReader.GetGuid(6);
                sysUser.IsAdmin = MyReader.GetInt32(7);
                sysUser.IsDownload = MyReader.GetInt32(8);
            }
            MyDB.DBClose();
            return sysUser;
        }

        public static SysUser GetUserViaName(string name)
        {
            SqlDataReader MyReader = null;
            SysUser sysUser = new SysUser();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            string strSQL = "select * from TB_SysUser where LoginName = @LoginName";
            MyDB.AddParameters(name, "LoginName", SqlDbType.VarChar);
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            MyReader = MyDB.DBReader;
            if (MyReader.Read())
            {
                sysUser.UserId = MyReader.GetGuid(0);
                sysUser.LoginName = MyReader.GetString(1);
                sysUser.UserName = MyReader.GetString(2);
                sysUser.UserEmail = MyReader.GetString(3);
                sysUser.RoleId = MyReader.GetGuid(6);
                sysUser.IsAdmin = MyReader.GetInt32(7);
                sysUser.IsDownload = MyReader.GetInt32(8);
            }
            MyDB.DBClose();
            return sysUser;
        }

        public static SqlDataReader GetUserViaRoleId(Guid roleId)
        {
            string strSQL = "Select * from tb_SysUser where RoleId=@RoleId order by UserName";
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(roleId, "RoleId", SqlDbType.UniqueIdentifier);
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            return MyDB.DBReader;
        }

        public static SqlDataReader GetUserViaUserFirstSpellName(string firstName, Guid areaId)
        {
            string strSQL = "Select tb_SysUser.* from tb_SysUser  left outer join TB_UserArea on tb_SysUser.UserId = TB_UserArea.UserId where substring(tb_SysUser.LoginName,1,1)=@FirstName and TB_UserArea.AreaId=@AreaId order by tb_SysUser.UserName";
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(firstName, "FirstName", SqlDbType.VarChar);
            MyDB.AddParameters(areaId, "AreaId", SqlDbType.UniqueIdentifier);
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            return MyDB.DBReader;
        }

        public static DataSet GetUserWithDataSet(Guid areaId, string strProvince, string strCat, string strRole, string strUserName)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(areaId, "AreaId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(new Guid(strProvince), "RegionId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(new Guid(strCat), "CatId", SqlDbType.UniqueIdentifier);
            if (strRole != "All")
            {
                MyDB.AddParameters(new Guid(strRole), "RoleId", SqlDbType.UniqueIdentifier);
            }
            MyDB.AddParameters("%" + strUserName + "%", "FirstName", SqlDbType.VarChar);
            StringBuilder sb = new StringBuilder("");
            sb.Append("select * from dbo.TB_SysUser  ");
            sb.Append("     where                                                                 ");
            int posWhereExpressionStart = sb.ToString().Length;
            sb.Append("UserId in                                                           ");
            sb.Append("( Select DISTINCT UserId from dbo.TB_UserArea where AreaId=@AreaId )   ");
            if (strProvince != "00000000-0000-0000-0000-000000000000")
            {
                sb.Append("and                                                                    ");
                sb.Append("userId in                                                               ");
                sb.Append("(Select DISTINCT UserId from dbo.TB_UserRegion where RegionId=@RegionId)   ");
            }
            if (strCat != "00000000-0000-0000-0000-000000000000")
            {
                sb.Append("and                                                                     ");
                sb.Append("userId in                                                              ");
                sb.Append("(Select DISTINCT UserId from dbo.TB_UserCAT where CatId=@CatId)            ");
            }
            if (strRole != "All")
            {
                sb.Append("and                                                                    ");
                sb.Append("TB_SysUser.RoleId =@RoleId                                             ");
            }
            if (strUserName != "All")
            {
                sb.Append("and                                                                       ");
                sb.Append("tb_SysUser.LoginName like @FirstName                             ");
            }
            sb.Append("   order by  LoginName                                                     ");
            string strSQL = sb.ToString();
            MyDB.DBSetSelect(strSQL);
            return MyDB.DBReturnDataSet();
        }

        public static void InsertNewAdminUser(string name, string loginName, string email, Guid roleId, int isDownload, Guid[] provinceId, Guid[] catId, Guid[] areaId, int isAdmin)
        {
            Guid id = Guid.NewGuid();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(id, "UserId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(name, "UserName", SqlDbType.VarChar);
            MyDB.AddParameters(loginName, "LoginName", SqlDbType.VarChar);
            MyDB.AddParameters(email, "UserEmail", SqlDbType.VarChar);
            MyDB.AddParameters(roleId, "RoleId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(isAdmin, "IsAdmin", SqlDbType.Int);
            MyDB.AddParameters(isDownload, "IsDownload", SqlDbType.Int);
            string strSQL = "Insert into Tb_SysUser (UserId,UserName,LoginName,UserEmail,RoleId,IsAdmin,IsDownload) values (@UserId,@UserName,@LoginName,@UserEmail,@RoleId,@IsAdmin,@IsDownload)";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
            InsertUserProvince(id, provinceId);
            InsertUserCat(id, catId);
            InsertUserArea(id, areaId);
        }

        public static void InsertNewUser(string name, string loginName, string email, Guid roleId, int isDownload, Guid[] provinceId, Guid[] catId, Guid[] areaId)
        {
            Guid id = Guid.NewGuid();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(id, "UserId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(name, "UserName", SqlDbType.VarChar);
            MyDB.AddParameters(loginName, "LoginName", SqlDbType.VarChar);
            MyDB.AddParameters(email, "UserEmail", SqlDbType.VarChar);
            MyDB.AddParameters(roleId, "RoleId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(isDownload, "IsDownload", SqlDbType.Int);
            string strSQL = "Insert into Tb_SysUser (UserId,UserName,LoginName,UserEmail,RoleId,IsDownload) values (@UserId,@UserName,@LoginName,@UserEmail,@RoleId,@IsDownload)";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
            InsertUserProvince(id, provinceId);
            InsertUserCat(id, catId);
            InsertUserArea(id, areaId);
        }

        public static void InsertUserArea(Guid userId, Guid[] areaId)
        {
            for (int i = 0; i < areaId.Length; i++)
            {
                Guid Id = Guid.NewGuid();
                DBAccess MyDB = new DBAccess();
                MyDB.DBOpen();
                MyDB.AddParameters(Id, "UserAreaId", SqlDbType.UniqueIdentifier);
                MyDB.AddParameters(userId, "UserId", SqlDbType.UniqueIdentifier);
                MyDB.AddParameters(areaId[i], "AreaId", SqlDbType.UniqueIdentifier);
                string strSQL = "Insert into TB_UserArea (UserAreaId,UserId,AreaId) values (@UserAreaId,@UserId,@AreaId)";
                MyDB.DBSetSelect(strSQL);
                MyDB.DBExcuteNonQuery();
                MyDB.DBClose();
            }
        }

        public static void InsertUserCat(Guid userId, Guid[] catId)
        {
            for (int i = 0; i < catId.Length; i++)
            {
                Guid Id = Guid.NewGuid();
                DBAccess MyDB = new DBAccess();
                MyDB.DBOpen();
                MyDB.AddParameters(Id, "UserCATId", SqlDbType.UniqueIdentifier);
                MyDB.AddParameters(userId, "UserId", SqlDbType.UniqueIdentifier);
                MyDB.AddParameters(catId[i], "CATId", SqlDbType.UniqueIdentifier);
                string strSQL = "Insert into TB_UserCAT (UserCATId,UserId,CATId) values (@UserCATId,@UserId,@CATId)";
                MyDB.DBSetSelect(strSQL);
                MyDB.DBExcuteNonQuery();
                MyDB.DBClose();
            }
        }

        public static void InsertUserProvince(Guid userId, Guid[] provinceId)
        {
            for (int i = 0; i < provinceId.Length; i++)
            {
                Guid Id = Guid.NewGuid();
                DBAccess MyDB = new DBAccess();
                MyDB.DBOpen();
                MyDB.AddParameters(Id, "UserRegionId", SqlDbType.UniqueIdentifier);
                MyDB.AddParameters(userId, "UserId", SqlDbType.UniqueIdentifier);
                MyDB.AddParameters(provinceId[i], "RegionId", SqlDbType.UniqueIdentifier);
                string strSQL = "Insert into TB_UserRegion (UserRegionId,UserId,RegionId) values (@UserRegionId,@UserId,@RegionId)";
                MyDB.DBSetSelect(strSQL);
                MyDB.DBExcuteNonQuery();
                MyDB.DBClose();
            }
        }

        public static bool IsExistSameUserName(string name)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(name, "LoginName", SqlDbType.VarChar);
            string strSQL = "select * from tb_SysUser where LoginName = @LoginName";
            MyDB.DBSetSelect(strSQL);
            if (MyDB.DBExecuteScalar() == null)
            {
                MyDB.DBClose();
                return false;
            }
            MyDB.DBClose();
            return true;
        }

        private static void RemoveUserAreaViaId(Guid userId)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(userId, "UserId", SqlDbType.UniqueIdentifier);
            string strSQL = "Delete From TB_UserArea where UserId = @UserId";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }

        private static void RemoveUserCatViaId(Guid userId)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(userId, "UserId", SqlDbType.UniqueIdentifier);
            string strSQL = "Delete From TB_UserCAT where UserId = @UserId";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }

        private static void RemoveUserProvinceViaId(Guid userId)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(userId, "UserId", SqlDbType.UniqueIdentifier);
            string strSQL = "Delete From TB_UserRegion where UserId = @UserId";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }

        public static void RemoveUserViaId(Guid id)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(id, "UserId", SqlDbType.UniqueIdentifier);
            string strSQL = "Delete From tb_SysUser where UserId = @UserId";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
            RemoveUserProvinceViaId(id);
            RemoveUserCatViaId(id);
            RemoveUserAreaViaId(id);
        }

        public static void UpdateUser(string name, string loginName, string email, Guid roleId, Guid userId, int isAdmin, int isDownload, Guid[] provinceId, Guid[] catId, Guid[] areaId)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(name, "UserName", SqlDbType.VarChar);
            MyDB.AddParameters(loginName, "LoginName", SqlDbType.VarChar);
            MyDB.AddParameters(email, "UserEmail", SqlDbType.VarChar);
            MyDB.AddParameters(roleId, "RoleId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(userId, "UserId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(isAdmin, "IsAdmin", SqlDbType.Int);
            MyDB.AddParameters(isDownload, "IsDownload", SqlDbType.Int);
            string strSQL = "Update Tb_SysUser set UserName = @UserName,LoginName = @LoginName,UserEmail = @UserEmail,RoleId = @RoleId,IsAdmin = @IsAdmin,IsDownload=@IsDownload where UserId = @UserId";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
            UpdateUserProvince(userId, provinceId);
            UpdateUserCat(userId, catId);
            UpdateUserArea(userId, areaId);
        }

        public static void UpdateUserArea(Guid userId, Guid[] areaId)
        {
            RemoveUserAreaViaId(userId);
            InsertUserArea(userId, areaId);
        }

        public static void UpdateUserCat(Guid userId, Guid[] catId)
        {
            RemoveUserCatViaId(userId);
            InsertUserCat(userId, catId);
        }

        public static void UpdateUserProvince(Guid userId, Guid[] provinceId)
        {
            RemoveUserProvinceViaId(userId);
            InsertUserProvince(userId, provinceId);
        }
    }
}

